package com.ncloud.crm.mapper;

import java.util.List;
import java.util.Map;

import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Select;

@Mapper
public interface UserDashboardMapper {
	//公有云节点：统计数字，用户的所有公有云节点数
	@Select(value = ("select count(distinct a.node_id) as count from bs_node a,bs_cust_user_rel b where a.cust_id"
			+ "=b.cust_id and b.user_id=#{userId} and a.node_type='0' and a.node_sub_type ='0'"))
	public int getCountOpenNode(String userId);

	//SWAN节点：统计数字，用户所有的CPE节点数
	@Select(value = ("select count(distinct a.node_id) as count from bs_node a,bs_cust_user_rel b where a.cust_id="
			+ "b.cust_id and b.user_id=#{userId} and a.node_type='1' and a.node_sub_type ='0'"))
	public int getCountSWANNode(String userId);

	//工单：统计数字，用户未处理的工单数
	@Select(value = ("SELECT COUNT(DISTINCT c.work_id) AS count FROM bs_o_work c,(SELECT o.order_id,o.cust_id,"
			+ "c.login_user_id FROM (SELECT order_id,cust_id FROM bs_o_order UNION ALL SELECT order_id,cust_id"
			+ " FROM bs_oh_order) o,(SELECT cust_id,user_id as login_user_id FROM bs_cust_user_rel ) c WHERE"
			+ " o.cust_id=c.cust_id and c.login_user_id"
			+ "=#{userId}) d where c.order_id = d.order_id and c.work_state !='3'"))
	public int getCountWork(String userId);
	
	//用户所有节点
	@Select(value = ("select c.attr_value,c.attr_code,ls.node_id,ls.cust_id,ls.node_name,ls.node_type from "
			+ "bs_node_item c inner join( select a.node_id,a.cust_id,a.node_name,a.node_type from bs_node a"
			+ ",bs_cust_user_rel b where a.cust_id=b.cust_id and b.user_id=#{userId} ) ls where c.node_id = "
			+ "ls.node_id and attr_code = 'bandWidth'"))
	public List<Map<Object,Object>> getNodeInfo(String userId);
	
	//用户节点的坐标
	@Select(value = ("select d.county_name,ls2.count from  bs_p_county_info d right join(select city,count(distinct "
			+ "c.node_id) as count from (select city,node_id from bs_eqpt_info union select inter_area as city,"
			+ "node_id from bs_public_cloud) c inner join (select a.node_id from bs_node a,bs_cust_user_rel b where "
			+ "a.cust_id=b.cust_id and b.user_id = #{userId} )ls on c.node_id=ls.node_id group by city)ls2 on "
			+ "d.county_id=ls2.city"))
	public List<Map<Object,Object>> getUserNodeCityByUserId(String userId);
}
